﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Collections.ObjectModel;

namespace Web4Comment
{
    public partial class statistics : System.Web.UI.Page
    {
        private string username;
        private string hostname;
        private int hostID;
        private string FCTsql;
        private string FCWsql;
        private string RHsql;
        private string RNTsql;
        private string Tsql;

        protected void Page_Load(object sender, EventArgs e)
        {
            hostname = (String)Session["username"];
            if (hostname == null || hostname == "")
            {
                Response.Redirect("./logon.aspx");
            }

            hostID = (Int32)Session["user_id"];

            FCTsql = "select username,secondsInterval from((select top 5 type_2, sum(x) as secondsInterval from (SELECT type_2, DATEDIFF(SECOND,start_time,end_time) as x FROM [UnlessComment].[dbo].[Time] where userid = " + hostID + "and type_1 = 1)A group by type_2 order by secondsInterval)B left join [UnlessComment].[dbo].[User]C on B.type_2 = C.id)";
            FCTDS.SelectCommand = FCTsql;

            FCWsql = "select top 5 reply_username, sum(text_length) as words from (select * from(select * from (SELECT [id],[user_id],DATALENGTH([content]) as text_length FROM [UnlessComment].[dbo].[Comment] where user_id = " + hostID + ")A left join (select comment_id,replied_id from [UnlessComment].[dbo].[Reply])B on A.id=B.comment_id where B.comment_id is not null)C left join (select user_id as reply_user_id, id as reply_comment_id from [UnlessComment].[dbo].[Comment])D on C.replied_id = D.reply_comment_id)E left join (select id as reply_user_id, username as reply_username from [UnlessComment].[dbo].[User])F on E.reply_user_id = F.reply_user_id group by reply_username order by words";
            FCWDS.SelectCommand = FCWsql;

            RHsql = "select total,D.catalog as catalog_name from (select sum(number) as total,[catalog] from(SELECT COUNT(*) as number,[type_2] FROM [UnlessComment].[dbo].[Time] where userid=" + hostID + " and type_1 = 2 group by type_2)A left join (select id,[catalog] from [UnlessComment].[dbo].[News])B on A.type_2 = B.id group by [catalog])C left join (select * from [UnlessComment].[dbo].[NewsCatalog])D on C.catalog = D.id order by total";
            RHDS.SelectCommand = RHsql;

            RNTsql = "select D.catalog as catalog_name, secondsInterval as total from (select [catalog], SUM(A.x) as secondsInterval from(SELECT SUM(DATEDIFF(SECOND,start_time,end_time)) as x,[type_2] FROM [UnlessComment].[dbo].[Time] where userid=" + hostID + " and type_1 = 2 group by type_2)A left join (select id,[catalog] from [UnlessComment].[dbo].[News])B on A.type_2 = B.id group by [catalog])C left join (select * from [UnlessComment].[dbo].[NewsCatalog])D on C.catalog = D.id order by secondsInterval";
            RNTDS.SelectCommand = RNTsql;

            Tsql = "SELECT case when type_1=1 then '好友聊天' else case when type_1 =2 then '浏览信息' else '维护信息' end end as time_type, sum(DATEDIFF(SECOND,start_time,end_time)) as total FROM [UnlessComment].[dbo].[Time] where userid = " + hostID + " group by type_1";
            TDS.SelectCommand = Tsql;

            
            
        }
    }
}